#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
执行yzh_resource表创建SQL脚本
作者：YZH
创建日期：2024-07-14
"""

import os
import mysql.connector
from mysql.connector import Error

def execute_sql_script(sql_file_path):
    """执行SQL脚本文件"""
    try:
        # 数据库连接配置
        db_config = {
            'host': '47.113.186.218',
            'user': 'root',
            'password': '2025@Bysj',
            'database': 'ai_teach',
            'port': 3306,
            'charset': 'utf8mb4'
        }
        
        # 读取SQL文件
        if not os.path.exists(sql_file_path):
            print(f"错误: SQL文件不存在 - {sql_file_path}")
            return False
        
        with open(sql_file_path, 'r', encoding='utf-8') as file:
            sql_content = file.read()
        
        # 分割SQL语句
        statements = [stmt.strip() for stmt in sql_content.split(';') if stmt.strip()]
        
        # 创建连接
        connection = mysql.connector.connect(**db_config)
        cursor = connection.cursor()
        
        print(f"成功连接到数据库 {db_config['database']}@{db_config['host']}")
        print(f"准备执行 {len(statements)} 条SQL语句...")
        
        # 执行每条SQL语句
        for i, statement in enumerate(statements, 1):
            try:
                cursor.execute(statement)
                print(f"[{i}/{len(statements)}] 执行成功: {statement[:60]}...")
            except Error as e:
                print(f"[{i}/{len(statements)}] 执行失败: {statement[:60]}...")
                print(f"错误信息: {str(e)}")
        
        # 提交事务
        connection.commit()
        print("所有SQL语句执行完毕，事务已提交")
        
        # 关闭连接
        cursor.close()
        connection.close()
        
        return True
    
    except Error as e:
        print(f"数据库错误: {str(e)}")
        return False
    
    except Exception as e:
        print(f"执行过程中发生错误: {str(e)}")
        return False

if __name__ == "__main__":
    # SQL文件路径
    script_dir = os.path.dirname(os.path.abspath(__file__))
    sql_file = os.path.join(script_dir, "create_yzh_resource_table.sql")
    
    print("开始执行SQL脚本...")
    result = execute_sql_script(sql_file)
    
    if result:
        print("SQL脚本执行成功!")
    else:
        print("SQL脚本执行失败!") 